﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Xml.Serialization;

namespace ExcelToData
{
    public partial class Form1 : Form
    {
        string XMLTableRoot = "Table";
        string XMLTableItem = "SubItem";
        string IrognHor = "2";
        ExcelPackage excelPackage;

        List<Dictionary<string, string>> table;
        string copytoOtherPath = "";
        public Form1()
        {
            IrognHor = AppConfig.Singleton.GetConfigString("DefaultIgnoreHoriz");
            copytoOtherPath =  AppConfig.Singleton.GetConfigString("CopyToOtherPath");
            InitializeComponent();
            IgnoreHorizNum.TextChanged -= IgnoreHorizNum_TextChanged;
            IgnoreHorizNum.Text = IrognHor;
            IgnoreHorizNum.TextChanged += IgnoreHorizNum_TextChanged;

            TableRootName.Text = XMLTableRoot;
            TableSubName.Text = XMLTableItem;

            Sheets.SelectionMode = SelectionMode.MultiExtended;
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.FileOk += OnOpenFile;
            openFile.Filter = "(Excel文件 *.xlsx)|*.xlsx";

            string DefualtPath = AppConfig.Singleton.GetConfigString("DefaultOpenPath");
            openFile.InitialDirectory = DefualtPath;
            openFile.Multiselect = false;
            openFile.ShowDialog();
        }

        private void OnOpenFile(object sender, CancelEventArgs e)
        {
            if (e.Cancel) return;
            OpenFileDialog openFile = sender as OpenFileDialog;
            string filepath = openFile.FileName;
            FileInfo info = new FileInfo(filepath);
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

            ShowSheets(info);


        }

                   

        void ShowSheets(FileInfo info)
        {
            excelPackage = new ExcelPackage(info);
            PathShow.Text = info.FullName;

            int sheetCount = excelPackage.Workbook.Worksheets.Count;
            Sheets.Items.Clear();
            for (int i = 0; i < sheetCount; i++)
            {
                string name = excelPackage.Workbook.Worksheets[i].Name;

                Sheets.Items.Add(name);
            }
            Sheets.SelectedIndex = 0;
            ////指定需要读入的sheet名
            //ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["sheet名字"];
            ////比如读取第一行,第一列的值数据
            //object a = excelWorksheet.Cells[1, 1].Value;
            ////读取第一行,第二列的值为
            //object b = excelWorksheet.Cells[1, 2].Value;
            ////然后根据需要对a，b转为字符串，或者double，int等..


        }

        string selectName;
        private void Sheets_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBox listBox = sender as ListBox;
            if (listBox.SelectedItem == null)
            {
                MessageBox.Show("请先打开表格文件");
                return;
            }
            selectName = listBox.SelectedItem.ToString();

            string[] selectArray = GetSelectItems();
            StringBuilder builder = new StringBuilder();
            foreach (var item in selectArray)
            {
                builder.Append(item);
                builder.Append(",");
            }
            SelectSheetName.Text = builder.ToString(); ;//selectName;

            ExcelWorksheet SelectSheet = excelPackage.Workbook.Worksheets[selectName];
            ReadSheet(SelectSheet);
        }

        string[] GetSelectItems()
        {
            int SelectNum = Sheets.SelectedItems.Count;
            string[] array = new string[SelectNum];
            for (int i = 0; i < Sheets.SelectedItems.Count; i++)
            {
                string name = Sheets.SelectedItems[i].ToString();
                array[i] = name;
            }
            return array;
        }

        void ReadSheet(ExcelWorksheet SelectSheet)
        {
            if (SelectSheet == null) return;
            List<string> Heads = new List<string>();
            //确定head个数
            int num = 1;
            while (true)
            {
                object a = SelectSheet.Cells[1, num].Value;
                if(a != null)
                {
                    Heads.Add(a.ToString());
                    num += 1;
                }
                else break;
            }

            //忽略列数
            int Ignore = 2;
            if (!string.IsNullOrEmpty(IgnoreHorizNum.Text))
            {
                int.TryParse(IgnoreHorizNum.Text, out Ignore);
            }

            //开始列
            int start = 1 + Ignore;
            table = new List<Dictionary<string, string>>();

            while (true)
            {
                string a = GetMegerValue(SelectSheet, start, 1);//SelectSheet.Cells[start, 1].Value;
                if (!string.IsNullOrEmpty(a))
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    for (int i = 0; i < Heads.Count; i++)
                    {
                        string col = GetMegerValue(SelectSheet, start, i + 1);//SelectSheet.Cells[start, i+1].Value;
                        if (!string.IsNullOrEmpty(col))
                            dic.Add(Heads[i], col.ToString());
                        else
                             dic.Add(Heads[i], "");
                    }
                    table.Add(dic);
                    start += 1;
                }
                else break;
            }

            string xml = CreateXMLContent(table);
            string json = CreateJsonContent(table);
            //显示
            XmlContent.Text = xml;
            JsonContent.Text = json;
        }

        public static string GetMegerValue(ExcelWorksheet wSheet, int row, int column)
        {
            string range = wSheet.MergedCells[row, column];
            if (range == null)
                if (wSheet.Cells[row, column].Value != null)
                    return wSheet.Cells[row, column].Value.ToString();
                else
                    return "";
            object value =
                wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
            if (value != null)
                return value.ToString();
            else
                return "";
        }


        string CreateXMLContent(List<Dictionary<string, string>> table)
        {
            //StringBuilder buffer = new StringBuilder();
            //XmlSerializer serializer = new XmlSerializer(table.GetType());
            //using (TextWriter writer = new StringWriter(buffer))
            //{
            //    serializer.Serialize(writer, table);
            //}
            //return buffer.ToString();
            string head = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
            string returnChar = "\r\n";
            string TableName = XMLTableRoot;
            string SubItemName = XMLTableItem;

            StringBuilder builder = new StringBuilder();
            builder.Append(head);
            builder.Append(returnChar);
            builder.Append($"<{TableName}>");//表开头

            if (XmlFormatStyle.Checked)
            {
                foreach (var item in table)
                {   builder.Append(returnChar);
                    builder.Append($"<{SubItemName}>");//item开头
                    builder.Append(returnChar);
                    foreach (var sub in item)
                    {
                        builder.Append($"<{sub.Key}>{sub.Value}</{sub.Key}>");
                        builder.Append(returnChar);
                    }
                    builder.Append($"</{SubItemName}>");//item开头
                    builder.Append(returnChar);
                }
            }
            else
            {
                foreach (var item in table)
                {
                    builder.Append($"<{SubItemName} ");//item开头
                    foreach (var sub in item)
                    {
                        builder.Append($"{sub.Key}=\"{sub.Value}\"");
                        builder.Append(" ");
                    }
                    builder.Append(">" + returnChar);
                    builder.Append($"</{SubItemName}>");//item开头
                    builder.Append(returnChar);
                }
            }

            builder.Append($"</{TableName}>");//表结尾

            return builder.ToString();
        }

        string CreateJsonContent(List<Dictionary<string, string>> table)
        {
            StringBuilder builder = new StringBuilder();
            return Newtonsoft.Json.JsonConvert.SerializeObject(table);
        }

        private void OnExportJsonClick(object sender, EventArgs e)
        {
            if (table == null)
            {
                MessageBox.Show("请先打开表格文件");
                return ;
            }

            SaveFileDialog save = new SaveFileDialog();
            save.FileOk += OnSaveJsonFile;
            save.Filter = "(Json文件 *.Json)|*.json";
            save.InitialDirectory = AppConfig.Singleton.GetConfigString("DefaultSavePath");
            if (Sheets.SelectedItems.Count > 1)
                save.FileName = "多个Json文件(用SheetName作为文件名)";
            else
                save.FileName = selectName + ".txt";
            save.ShowDialog();

        }
        private void OnExportXMLClick(object sender, EventArgs e)
        {
            if (table == null)
            {
                MessageBox.Show("请先打开表格文件");
                return ;
            }
            SaveFileDialog save = new SaveFileDialog();
            XmlContent.Text = CreateXMLContent(table);
            save.FileOk += OnSaveXMLFile;
            save.Filter = "(XML文件 *.Xml)|*.xml";
            save.InitialDirectory = AppConfig.Singleton.GetConfigString("DefaultSavePath");
            if (Sheets.SelectedItems.Count > 1)
                save.FileName = "多个Xml文件(用SheetName作为文件名)";
            else
                save.FileName = selectName + ".xml";
            save.ShowDialog();
        }

        private void OnSaveXMLFile(object sender, CancelEventArgs e)
        {
            SaveFileDialog save = sender as SaveFileDialog;
            string filepath = save.FileName;

            string[] selectArray = GetSelectItems();
            if (selectArray.Length == 1)
            {
                File.WriteAllText(filepath, XmlContent.Text);
            }
            else
            {
                foreach (var item in selectArray)
                {
                    FileInfo sinfo = new FileInfo(filepath);
                    string itempath = sinfo.Directory + "/" + item + ".xml";

                    ExcelWorksheet SelectSheet = excelPackage.Workbook.Worksheets[item];
                    ReadSheet(SelectSheet);
                    File.WriteAllText(itempath, XmlContent.Text);
                    if (!string.IsNullOrEmpty(copytoOtherPath) && copytoOtherPath != "null")
                    {
                        FileInfo info = new FileInfo(itempath);
                        info.CopyTo(copytoOtherPath + "/" + info.Name, true);
                    }
                }
            }


            MessageBox.Show("完成.");
        }



        private void OnSaveJsonFile(object sender, CancelEventArgs e)
        {
            SaveFileDialog save = sender as SaveFileDialog;
            string filepath = save.FileName;

            string[] selectArray = GetSelectItems();
            if (selectArray.Length == 1)
            {
                File.WriteAllText(filepath, JsonContent.Text);
            }
            else
            {
                foreach (var item in selectArray)
                {
                    FileInfo sinfo = new FileInfo(filepath);
                    string itempath = sinfo.Directory + "/" + item + ".txt";

                    ExcelWorksheet SelectSheet = excelPackage.Workbook.Worksheets[item];
                    ReadSheet(SelectSheet);
                    File.WriteAllText(itempath, JsonContent.Text);
                    if (!string.IsNullOrEmpty(copytoOtherPath) && copytoOtherPath != "null")
                    {
                        FileInfo info = new FileInfo(itempath);
                        info.CopyTo(copytoOtherPath + "/" + info.Name, true);
                    }
                }
            }


            MessageBox.Show("完成.");
        }

        private void IgnoreHorizNum_TextChanged(object sender, EventArgs e)
        {
            Sheets_SelectedIndexChanged(Sheets, null);
        }

        private void TableRootName_TextChanged(object sender, EventArgs e)
        {
            TextBox text = sender as TextBox;
            XMLTableRoot = text.Text;
        }

        private void TableSubName_TextChanged(object sender, EventArgs e)
        {
            TextBox text = sender as TextBox;
            XMLTableItem = text.Text;
        }
    }
}
